//
//  FMDBUtils.swift
//  SwiftDemo
//
//  Created by apple on 2019/7/7.
//  Copyright © 2019 yimiSun. All rights reserved.
//

import Foundation

//数据库文件
private let K_APP_DB_PATH:String = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last! + "/AppDBFile.sqlite"

/** 用户登录名信息表 */
private let K_FMDB_USER_LOGIN_INFO:String = "users_login_infos"
private let K_FMDB_USER_TOUCHID_LOGIN_INFO:String = "users_touchid_login_info"

/** 商城信息表 */
private let K_FMDB_MALL_INFO:String = "mall_info"

/** 商城购物车表 */
private let K_FMDB_MALL_CAR_INFO:String = "mall_car_info"

/** 商城搜索信息表 */
private let K_FMDB_MALL_SEARCH_INFO:String = "mall_search_info"

/** 进销存商品信息表 */
private let K_FMDB_JXC_GOODS_INFO:String = "jxc_goods_info"

/** app请求日志信息表 */
private let K_FMDB_APP_LOG_INFO:String = "app_log_info"

/**
 * SQLite 数据库工具类
 */
struct FMDBUtils {
    
    /** FMDatabaseQueue */
    private let dataBaseQueue:FMDatabaseQueue? = FMDatabaseQueue.init(path: K_APP_DB_PATH)
    
    /**
     * SqliteDBUtils 单例
     */
    public static let shareInstance = FMDBUtils.init()
    
    
    //MARK: -
    /** 初始化数据库和表 */
    public func initDBAndTables() {
        
        var isSuccess:Bool = false
        
        //创建表
        let arrSqls:[String] = [
            String.init(format: "CREATE TABLE IF NOT EXISTS `%@`('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'user_name' TEXT UNIQUE,'user_password' TEXT DEFAULT '','is_remember' TINYINT DEFAULT 0);", K_FMDB_USER_LOGIN_INFO),
            String.init(format: "CREATE TABLE IF NOT EXISTS `%@`('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'user_name' TEXT UNIQUE,'user_password' TEXT DEFAULT '','user_remark' TEXT DEFAULT '');", K_FMDB_USER_TOUCHID_LOGIN_INFO),
            String.init(format:"CREATE TABLE IF NOT EXISTS '%@'('uId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'adjustPretaxPrice' REAL,'adjustPrice' REAL,'adjustRatio' REAL,'barCode' TEXT,'brand' TEXT,'calculatePrice' NUMERIC,'catalog' TEXT,'category' TEXT,'clientGoodsId' TEXT,'clientId' TEXT,'core' NUMERIC,'delivery' INTEGER,'deliveryClaim' TEXT,'description' TEXT,'expiry' TEXT,'freightTemplateId' INTEGER,'goldenTax' TEXT,'goodsEnabled' NUMERIC,'height' REAL,'id' TEXT UNIQUE,'images' TEXT,'length' REAl,'mallFeatured' TEXT,'mallSales' NUMERIC,'memo' TEXT,'minOrder' REAl,'model' TEXT,'name' TEXT,'orgFullname' TEXT,'packageNum' REAL,'packageUnit' TEXT,'packaging' TEXT,'place' TEXT,'deliveryPlace' TEXT,'platformSku' TEXT,'pretaxPrice' REAl,'price' REAL,'profileEnabled' NUMERIC,'reviewContent' TEXT,'reviewStatus' TEXT,'safeStock' INTEGER,'saled' NUMERIC,'spec' TEXT,'stock' REAL,'stockDelivery' INTEGER,'taxRate' REAL,'technical' TEXT,'type' TEXT,'unit' TEXT,'unspsc' TEXT,'vendorId' INTEGER,'vendorSku' TEXT,'warranty' TEXT,'weight' REAL,'width' REAL,'buyCount' INTEGER);",K_FMDB_MALL_INFO),
            String.init(format:"CREATE TABLE IF NOT EXISTS '%@'('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'keyword' TEXT UNIQUE DEFAULT '');",K_FMDB_MALL_SEARCH_INFO),
            String.init(format:"CREATE TABLE IF NOT EXISTS '%@'('cId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'adjustPretaxPrice' REAL,'adjustPrice' REAL,'adjustRatio' REAL,'barCode' TEXT,'brand' TEXT,'calculatePrice' NUMERIC,'catalog' TEXT,'category' TEXT,'clientGoodsId' TEXT,'clientId' TEXT,'core' NUMERIC,'delivery' INTEGER,'deliveryClaim' TEXT,'description' TEXT,'expiry' TEXT,'freightTemplateId' INTEGER,'goldenTax' TEXT,'goodsEnabled' NUMERIC,'height' REAL,'id' TEXT UNIQUE,'images' TEXT,'length' REAl,'mallFeatured' TEXT,'mallSales' NUMERIC,'memo' TEXT,'minOrder' REAl,'model' TEXT,'name' TEXT,'orgFullname' TEXT,'packageNum' REAL,'packageUnit' TEXT,'packaging' TEXT,'place' TEXT,'deliveryPlace' TEXT,'platformSku' TEXT,'pretaxPrice' REAl,'price' REAL,'profileEnabled' NUMERIC,'reviewContent' TEXT,'reviewStatus' TEXT,'safeStock' INTEGER,'saled' NUMERIC,'spec' TEXT,'stock' REAL,'stockDelivery' INTEGER,'taxRate' REAL,'technical' TEXT,'type' TEXT,'unit' TEXT,'unspsc' TEXT,'vendorId' INTEGER,'vendorSku' TEXT,'warranty' TEXT,'weight' REAL,'width' REAL,'buyCount' INTEGER);",K_FMDB_MALL_CAR_INFO),
            String.init(format:"CREATE TABLE IF NOT EXISTS '%@'('uId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'adjustPretaxPrice' REAL,'adjustPrice' REAL,'adjustRatio' REAL,'barCode' TEXT,'brand' TEXT,'calculatePrice' NUMERIC,'catalog' TEXT,'category' TEXT,'clientGoodsId' TEXT,'clientId' TEXT,'core' NUMERIC,'delivery' INTEGER,'deliveryClaim' TEXT,'description' TEXT,'expiry' TEXT,'freightTemplateId' INTEGER,'goldenTax' TEXT,'goodsEnabled' NUMERIC,'height' REAL,'id' TEXT UNIQUE,'images' TEXT,'length' REAl,'mallFeatured' TEXT,'mallSales' NUMERIC,'memo' TEXT,'minOrder' REAl,'model' TEXT,'name' TEXT,'orgFullname' TEXT,'packageNum' REAL,'packageUnit' TEXT,'packaging' TEXT,'place' TEXT,'deliveryPlace' TEXT,'platformSku' TEXT,'pretaxPrice' REAl,'price' REAL,'profileEnabled' NUMERIC,'reviewContent' TEXT,'reviewStatus' TEXT,'safeStock' INTEGER,'saled' NUMERIC,'spec' TEXT,'stock' REAL,'stockDelivery' INTEGER,'taxRate' REAL,'technical' TEXT,'type' TEXT,'unit' TEXT,'unspsc' TEXT,'vendorId' INTEGER,'vendorSku' TEXT,'warranty' TEXT,'weight' REAL,'width' REAL,'buyCount' INTEGER,'createTime' TEXT DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),'updateTime' timestamp DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')));",K_FMDB_JXC_GOODS_INFO),
            String.init(format:"CREATE TABLE IF NOT EXISTS '%@'('lId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'uId' TEXT,'userName' TEXT,'requestUrl' TEXT DEFAULT '','beginTime' TEXT DEFAULT '','endTime' TEXT DEFAULT '','duration' REAL,'statusCode' INTEGER,'token' TEXT,'method' TEXT DEFAULT 'GET','orgType' TEXT,'remark' TEXT,'netWork' TEXT);",K_FMDB_APP_LOG_INFO),
        ]
        
        dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            for strSQL:String in arrSqls {
                isSuccess = db?.executeUpdate(strSQL, withParameterDictionary: nil) ?? false
                if isSuccess == false {
                    break
                }
            }
        })
        
        if isSuccess == true {
            print("表初始化成功")
        }
        else{
            print("表初始化失败")
        }
    }
    
    
    //MARK: - 登录密码
    /** 记住密码 */
    func keepPasswordFor(User uid:String,
                         andPwd pwd:String,
                         withRemember r:Bool = false) -> Bool {
        let dicTemp = [
            "user_name":uid,
            "user_password":pwd,
            "is_remember": r ? "1" : "0"
        ]
        let _r = self.addDataForDic(dic: dicTemp, AndTable: K_FMDB_USER_LOGIN_INFO)
        return _r
    }
    
    /** 查找密码 */
    func getPwdFor(User uid:String,andIskeep k:Bool = true) -> String? {
        let _arrTemp:[[String:Any]]? = self.getAllValuesFromTable(tbName: K_FMDB_USER_LOGIN_INFO,
                                                                  AndWhere: "WHERE `user_name` = '\(uid)' AND `is_remember` = \(k ? 1 : 0)",
                                                                  WithOrderBy: "ORDER BY `id` DESC LIMIT 1")
        
        if _arrTemp != nil && _arrTemp!.count > 0 {
            return _arrTemp!.first!["user_password"] as? String
        }
        else {
            return nil
        }
    }
    
    /** 移除密码 */
    func removePwdFor(User uid:String) -> Bool {
        let result:Bool = self.removeDataForPrimaryKey(strKey: "user_name",
                                                       andValue: uid,
                                                       andTable: K_FMDB_USER_LOGIN_INFO)
        return result
    }
    
    
    //MARK: - 商城
    /// 是否已收藏
    /// - Parameter sid: 商品编号
    /// - Returns: true 已收藏 false 未收藏
    func isCollectFor(Id sid:String) -> Bool {
        let _arrTemp:[[String:String]]? = self.getAllValuesFromTable(tbName: K_FMDB_MALL_INFO,
                                                                     AndWhere: " WHERE `id` = '\(sid)'",
                                                                     WithOrderBy: " ORDER BY `uId` DESC LIMIT 1")
        
        if _arrTemp != nil && _arrTemp!.count == 1 {
            return true
        }
        else {
            return false
        }
    }
    
    /// 查询所有收藏的商品
    /// - Returns: [[String:String]]?
    func getCollectList() -> [[String:String]]? {
        let _arrTemp:[[String:String]]? = self.getAllValuesFromTable(tbName: K_FMDB_MALL_INFO,
                                                                     AndWhere: nil,
                                                                     WithOrderBy: " ORDER BY `uId` DESC")
        return _arrTemp
    }
    
    
    /** 移除收藏 */
    func removeCollectFor(Id sid:String) -> Bool {
        let result:Bool = self.removeDataForPrimaryKey(strKey: "id",
                                                       andValue: sid,
                                                       andTable: K_FMDB_MALL_INFO)
        return result
    }
    
    
    //MARK: - 商品搜索历史
    /// 添加搜索历史记录
    /// - Parameter t: String
    /// - Returns: true 成功
    func addSearchHistoryFor(Text t:String) -> Bool {
        let dicTemp = ["keyword":t]
        let _r = self.addDataForDic(dic: dicTemp, AndTable: K_FMDB_MALL_SEARCH_INFO)
        return _r
    }
    
    
    /// 清除历史记录
    /// - Returns: true 成功
    func removeSearchHistory() -> Bool {
        let result:Bool = self.removeAllDataForTableName(tbName: K_FMDB_MALL_SEARCH_INFO)
        return result
    }
    
    /// 获取搜索历史记录
    /// - Returns: [String]?
    func getAllHistory() -> [String]? {
        let _arrTemp:[[String:String]]? = self.getAllValuesFromTable(tbName: K_FMDB_MALL_SEARCH_INFO,
                                                                     AndWhere: nil,
                                                                     WithOrderBy: " ORDER BY `id` DESC")
        if _arrTemp == nil { return nil }
        return _arrTemp!.map({ (_dic:[String : String]) -> String in
            return "\(_dic["keyword"]!)"
        })
    }
    
    
    //MARK: - App请求日志
    /// 记录接口请求的时间
    /// - Parameters:
    ///   - url: 请求地址
    ///   - bt: 开始时间(yyyy-MM-dd HH:mm:ss)
    ///   - et: 结束时间(yyyy-MM-dd HH:mm:ss)
    ///   - st: 状态码(Status Code)
    ///   - mt: 请求方式(GET、POST、PUT、DELETE......)
    ///   - rm: 备注信息
    /// - Returns: true 记录成功
    func addAppRequestLogFor(Url url:String,
                             andBeginTime bt:String,
                             andEndTime et:String,
                             andStatusCode st:Int,
                             withMethod mt:String = "GET",
                             withRemark rm:String = "",
                             withDuration _duration:TimeInterval? = nil) -> Bool {
        
        //持续时间(单位：秒)
        var duration:TimeInterval!
        if _duration == nil {
            duration = Utils.shareInstance().getUnixtimeForStringDate(strDateTime: et) - Utils.shareInstance().getUnixtimeForStringDate(strDateTime: bt)
        }
        else{
            duration = _duration
        }
        
        let _dicParams:[String:String] = [
            "requestUrl":url,
            "beginTime":bt,
            "endTime":et,
            "duration":String.init(format:"%.3f",duration),
            "statusCode":"\(st)",
            "token":"",
            "method":mt,
            "uId":"",
            "userName":"",
            "orgType":"",
            "remark":rm,
            "netWork":AppDelegate.netWorkType ?? "" //网络类型
        ]
        
        let _result = self.addDataForDic(dic: _dicParams,
                                         AndTable: K_FMDB_APP_LOG_INFO)
        
        print("app请求日志记录\(_result ? "成功":"失败")")
        return _result
    }
    
    
    //MARK: - 增、删、改、查
    /** 插入数据 */
    private func addDataForDic(dic:[String:String]?,
                               AndTable tbName:String) -> Bool {
        if dic == nil || (dic?.keys.count)! <= 0 {
            return false
        }
        
        let strColumns:String = String.init(format: "%@", dic!.keys.joined(separator: ","))
        let strValues:String = String.init(format: "%@", dic!.values.map({ return $0.sqliteEscape() }).joined(separator: "','"))
        
        // replace into （insert into 的增强版）如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则，直接插入新数据。
        let strSQL:String = String.init(format: "REPLACE INTO `%@`(%@) VALUES('%@');", tbName,strColumns,strValues)
        print(strSQL)
        
        var isOK:Bool = false
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOK = db?.executeUpdate(strSQL, withParameterDictionary: nil) ?? false
        })
        return isOK
    }
    
    /**
     * 获取所有数据
     * @param tbName 表名称
     * @return 返回所有数据的数组
     */
    private func getAllValuesFromTable(tbName:String,
                                       AndWhere strWere:String?,
                                       WithOrderBy strOrderBy:String?) -> [[String:String]] {
        
        var strColumnInfo:String?
        var arrInfo:[[String:String]] = [[String:String]]()
        
        let strSQL:String = String.init(format:"SELECT * FROM `%@` %@ %@;",tbName,strWere ?? "",strOrderBy ?? "")
        print(strSQL)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            let resultSet:FMResultSet? = db?.executeQuery(strSQL, withArgumentsIn: nil)
            defer {
                resultSet?.close()
            }
            
            if resultSet != nil {
                var dicTemp:[String:String]?
                while resultSet!.next() {
                    dicTemp = [String:String]()
                    
                    for i in 0..<resultSet!.columnCount() {
                        strColumnInfo = resultSet!.columnName(for:i)
                        
                        if strColumnInfo != nil && !strColumnInfo!.isEmpty {
                            dicTemp![strColumnInfo!] = resultSet!.string(forColumn: strColumnInfo!)
                        }
                    }
                    
                    //添加到数组
                    arrInfo.append(dicTemp!)
                }
            }
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return arrInfo
    }
    
    
    /** 根据主键删除信息 */
    private func removeDataForPrimaryKey(strKey:String,
                                         andValue v:String,
                                         andTable tbName:String) -> Bool {
        var isOk:Bool = false
        let strSQL:String = String.init(format:"DELETE FROM `%@` WHERE `%@` = '%@';",tbName,strKey,v)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOk = db?.executeUpdate(strSQL, withArgumentsIn: nil) ?? false
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return isOk
    }
    
    
    /// 批量删除
    /// - Parameters:
    ///   - strKey: 列名
    ///   - vs: 删除的条件值
    ///   - tbName: 表名
    /// - Returns: true 成功
    private func removeDataForPrimaryKey(strKey:String,
                                         andValues vs:[String],
                                         andTable tbName:String) -> Bool {
        
        let _values:String = vs.map { return "'\($0)'" }.joined(separator: ",")
        
        var isOk:Bool = false
        let strSQL:String = String.init(format:"DELETE FROM `%@` WHERE `%@` IN(%@);",tbName,strKey,_values)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOk = db?.executeUpdate(strSQL, withArgumentsIn: nil) ?? false
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return isOk
    }
    
    
    /// 更新表数据
    /// - Parameters:
    ///   - k: 主键
    ///   - kv: 主键值
    ///   - dicV: 更新的字段及值信息
    ///   - tbName: 表名
    /// - Returns: true 成功
    private func updateDataForPrimaryKey(strKey k:String,
                                         andKeyValue kv:String,
                                         andUpdates dicV:[String:String],
                                         andTable tbName:String) -> Bool {
        
        let strUpdate = dicV.map { return "`\($0.key)` = '\($0.value)'" }.joined(separator: ",")
        
        var isOk:Bool = false
        let strSQL:String = String.init(format:"UPDATE `%@` SET %@ WHERE `%@` = '%@';",tbName,strUpdate,k,kv)
        print(strSQL)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOk = db?.executeUpdate(strSQL, withArgumentsIn: nil) ?? false
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return isOk
    }
    
    /**
     * 删除表中所有信息
     * @param tbName NSString 表名
     */
    private func removeAllDataForTableName(tbName:String) -> Bool {
        var isOk:Bool = false;
        let strSQL:String = String.init(format:"DELETE FROM `%@`;",tbName)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOk = db?.executeUpdate(strSQL, withArgumentsIn: nil) ?? false
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return isOk
    }
}


//MARK: - TouchID登录
extension FMDBUtils {
    
    /**! 添加TouchID登录信息 */
    func addTouchIdInfoFor(User uid:String,
                           andPwd pwd:String) -> Bool {
        let dicTemp = [
            "user_name":uid,
            "user_password":pwd
        ]
        let _r = self.addDataForDic(dic: dicTemp, AndTable: K_FMDB_USER_TOUCHID_LOGIN_INFO)
        return _r
    }
    
    /**! 移除密码 */
    func removeTouchIDInfoFor(User uid:String) -> Bool {
        let result:Bool = self.removeDataForPrimaryKey(strKey: "user_name",
                                                       andValue: uid,
                                                       andTable: K_FMDB_USER_TOUCHID_LOGIN_INFO)
        return result
    }
    
    /**!
     * 查找最新的TouchID(指纹登录时使用)
     * @return (用户名,密码)
     */
    func getTouchIDInfo() -> (String?,String?) {
        let _arrTemp:[[String:Any]]? = self.getAllValuesFromTable(tbName: K_FMDB_USER_TOUCHID_LOGIN_INFO,
                                                                  AndWhere: nil,
                                                                  WithOrderBy: "ORDER BY `id` DESC LIMIT 1")
        
        if _arrTemp != nil && _arrTemp!.count > 0 {
            return (
                _arrTemp!.first!["user_name"] as? String,
                _arrTemp!.first!["user_password"] as? String
            )
        }
        else {
            return (nil,nil)
        }
    }
    
}
